﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;

using EdgeStatz.ESSystems.Objects;

namespace EdgeStatz.ModelEngine.DatabaseTools
{
    class DatabaseToolsXml
    {
        #region Instance Variables

        private string xmlFilePath = null;
        private string modelName = null;
        private string modelPath = null;
        private string databaseName = null;
        private string databasePath = null;

        private List<TableDefinition> tableArray = new List<TableDefinition> { };
        private List<Provider> providerArray = new List<Provider> { };

        private bool option_recursive = false;
        private EmailServer option_emailserver;
        private bool option_logSql = false;
        private bool option_save_auto = false;
        private bool option_save_request = false;

        public bool Option_LogSql
        {
            get { return option_logSql; }
            set { option_logSql = value; }
        }

        public EmailServer Option_EmailServer
        {
            get { return option_emailserver; }
            set { option_emailserver = value; }
        }

        public bool Option_Recursive
        {
            get { return option_recursive; }
            set { option_recursive = value; }
        }

        public bool Option_Save_Auto
        {
            get { return option_save_auto; }
            set { option_save_auto = value; }
        }

        public bool Option_Save_Request
        {
            get { return option_save_request; }
            set { option_save_request = value; }
        }

        #endregion

        #region Properties
        /// <summary>
        /// Get the name of the model
        /// </summary>
        public string ModelName
        {
            get { return modelName; }
            set { modelName = value; }
        }
        /// <summary>
        /// Get the name of the database (in the model file)
        /// </summary>
        public string DatabaseName
        {
            get { return databaseName; }
            set { databaseName = value; }
        }
        /// <summary>
        /// Get the file path to save the database files
        /// </summary>
        public string DatabasePath
        {
            get { return databasePath; }
            set { databasePath = value; }
        }
        /// <summary>
        /// Get the number of tables defined in the model
        /// </summary>
        public int TableCount
        {
            get { return tableArray.Count; }
        }
        /// <summary>
        /// Get or Set the array of table definitions
        /// </summary>
        public TableDefinition[] Tables
        {
            set
            {
                tableArray = new List<TableDefinition> { };
                tableArray.AddRange(value);
            }
            get { return tableArray.ToArray(); }
        }
        /// <summary>
        /// Get or Set the array of data providers
        /// </summary>
        public Provider[] DataProviders
        {
            set
            {
                providerArray = new List<Provider> { };
                providerArray.AddRange(value);
            }
            get { return providerArray.ToArray(); }
        }
        /// <summary>
        /// Get or Set the file path of the model file. (reloads the file on Set)
        /// </summary>
        public string XmlFilePath
        {
            set { 
                xmlFilePath = value;
                CancelChanges();
            }
            get { return xmlFilePath; }
        }
        /// <summary>
        /// Get or Set the file path of the model directory
        /// </summary>
        public string ModelPath
        {
            get { return modelPath; }
            set { modelPath = value; }
        }
        #endregion

        #region Constructors

        public DatabaseToolsXml(string path) 
        {
            xmlFilePath = path;
            CancelChanges();
        }
        #endregion

        #region Table Methods
        /// <summary>
        /// Checks if a table exists by the name of the table
        /// </summary>
        /// <param name="name">Table name</param>
        /// <returns>true if table exists; false otherwise</returns>
        public bool TableExists(string name)
        {
            foreach (TableDefinition table in this.Tables)
            {
                if (table.Name.Equals(name))
                    return true;
            }
            return false;
        }
        /// <summary>
        /// Gets a table definition by the name of the table.
        /// </summary>
        /// <param name="name">Table name</param>
        /// <returns>First Table definition with the input name</returns>
        public TableDefinition GetTable(string name)
        {
            foreach (TableDefinition table in this.Tables)
            {
                if (table.Name.Equals(name))
                    return table;
            }
            return null;
        }
        /// <summary>
        /// Gets a table definition by index of table in array.
        /// </summary>
        /// <param name="index"></param>
        /// <returns>Table definition at the input index</returns>
        public TableDefinition GetTable(int index)
        {
            if (index >= tableArray.Count || index < 0)
                return null;
            return (TableDefinition)tableArray[index];
        }
        /// <summary>
        /// Get an array of all table names.
        /// </summary>
        /// <returns>string[] of all table names</returns>
        public string[] GetTableNames()
        {
            string[] array = new string[TableCount];
            for (int i = 0; i < TableCount; i++)
                array[i] = ((TableDefinition)tableArray[i]).Name;
            return array;
        }
        #endregion

        #region Read & Write
        
        private void Init() 
        {
            if (XmlFilePath == null)
                throw new NullReferenceException("Error: Model file not selected.");

            XmlDocument myXmlDocument = new XmlDocument();
            myXmlDocument.Load(XmlFilePath);

            XmlNode rootNode;
            rootNode = myXmlDocument.DocumentElement;

            if (rootNode.Name.ToUpper() != "MODEL")
                throw new Exception("Model file format incorrect.");
            
            modelName = rootNode.Attributes["Name"] == null ? null : rootNode.Attributes["Name"].Value;
            modelPath = rootNode.Attributes["Path"] == null ? null : rootNode.Attributes["Path"].Value;
            
            if (modelPath != null && !modelPath.EndsWith("\\"))
                modelPath += "\\";

            tableArray.Clear();
            providerArray.Clear();

            foreach (XmlNode node1 in rootNode.ChildNodes)
            {
                if (node1.Name.ToUpper() == "DATABASE")
                {
                    databaseName = node1.Attributes["Name"].Value;
                    databasePath = node1.Attributes["Path"].Value;

                    foreach (XmlNode node2 in node1.ChildNodes)
                    {
                        switch (node2.Name.ToUpper())
                        {
                            case "PROVIDER":
                                Provider provider = new Provider();
                                provider.Database = databaseName;
                                provider.Name = node2.Attributes["Name"] == null ? null : node2.Attributes["Name"].Value;
                                provider.Server = node2.Attributes["Server"] == null ? null : node2.Attributes["Server"].Value;
                                provider.UserId = node2.Attributes["UserId"] == null ? null : node2.Attributes["UserId"].Value;
                                provider.Password = node2.Attributes["Password"] == null ? null : node2.Attributes["Password"].Value;
                                if (node2.Attributes["Default"] != null && node2.Attributes.GetNamedItem("Default").Value.ToUpper().Equals("TRUE"))
                                    provider.IsDefault = true;
                                providerArray.Add(provider);
                                break;
                            case "TABLE":
                                TableDefinition table = new TableDefinition(node2.Attributes["Name"].Value);
                                foreach (XmlNode node3 in node2.ChildNodes)
                                {
                                    if (node3.Name.ToUpper() == "COLUMN")
                                    {
                                        string name = node3.Attributes["Name"] == null ? null : node3.Attributes["Name"].Value;
                                        ColumnType type;
                                        switch (node3.Attributes["Type"].Value.ToUpper())
                                        {
                                            case "BIT":
                                                type = ColumnType.Bit;
                                                break;
                                            case "DATETIME":
                                                type = ColumnType.Datetime;
                                                break;
                                            case "FLOAT":
                                                type = ColumnType.Float;
                                                break;
                                            case "GUID":
                                                type = ColumnType.GUID;
                                                break;
                                            case "INT":
                                                type = ColumnType.Int;
                                                break;
                                            case "VARCHAR":
                                                type = ColumnType.Varchar;
                                                break;
                                            case "DECIMAL":
                                                goto case "FLOAT";
                                            default:
                                                throw new NullReferenceException("Column type not found");
                                        }
                                        bool pk = node3.Attributes["Key"] == null ? false : node3.Attributes["Key"].Value.ToUpper().Contains("PK") ? true : false;
                                        bool nullable = node3.Attributes["Null"] == null ? true : node3.Attributes["Null"].Value.ToUpper().Equals("FALSE") ? false : true;
                                        string defaultValue = node3.Attributes["Default"] == null ? null : node3.Attributes["Default"].Value;
                                        ForeignKey foreignKey = null;
                                        if (node3.Attributes["Key"] != null && node3.Attributes["Key"].Value.ToUpper().Contains("FK")
                                            && node3.Attributes["ForeignKey"] != null)
                                        {
                                            string[] fk = node3.Attributes["ForeignKey"].Value.Split('.');
                                            foreignKey = new ForeignKey(fk[0], fk[1]);
                                        }
                                        int length = node3.Attributes["Length"] == null ? -1 : Convert.ToInt32(node3.Attributes["Length"].Value);

                                        table.AddColumn(new ColumnDefinition(name, type, pk, nullable, defaultValue, foreignKey, length));
                                    }
                                }
                                tableArray.Add(table);
                                break;
                            default:
                                break;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// Save all changes to the model file.
        /// </summary>
        public void SaveFile()
        {
            XmlDocument myXmlDocument = new XmlDocument();
            myXmlDocument.Load(XmlFilePath);

            XmlNode rootNode;
            rootNode = myXmlDocument.DocumentElement;
            rootNode.Attributes["Name"].Value = modelName;
            rootNode.Attributes["Path"].Value = modelPath;

            foreach (XmlNode node1 in rootNode.ChildNodes)
            {
                if (node1.Name.ToUpper() == "DATABASE")
                {
                    XmlNode dbNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Database", "");
                    XmlAttribute dbNameAtr = myXmlDocument.CreateAttribute("Name");
                    XmlAttribute dbPathAtr = myXmlDocument.CreateAttribute("Path");
                    dbNameAtr.Value = databaseName;
                    dbPathAtr.Value = databasePath;

                    dbNode.Attributes.Append(dbNameAtr);
                    dbNode.Attributes.Append(dbPathAtr);

                    foreach (Provider dataprov in DataProviders)
                    {
                        XmlNode dpnode = myXmlDocument.CreateNode(XmlNodeType.Element, "Provider", "");
                        XmlAttribute dpNameAtr = myXmlDocument.CreateAttribute("Name");
                        XmlAttribute dpServerAtr = myXmlDocument.CreateAttribute("Server");
                        XmlAttribute dpUidAtr = myXmlDocument.CreateAttribute("UserId");
                        XmlAttribute dpPwdAtr = myXmlDocument.CreateAttribute("Password");
                        XmlAttribute dpDefaultAtr = myXmlDocument.CreateAttribute("Default");

                        dpNameAtr.Value = dataprov.Name;
                        dpServerAtr.Value = dataprov.Server;
                        dpUidAtr.Value = dataprov.UserId;
                        dpPwdAtr.Value = dataprov.Password;
                        dpDefaultAtr.Value = dataprov.IsDefault ? "true" : "false";

                        dpnode.Attributes.Append(dpNameAtr);
                        dpnode.Attributes.Append(dpServerAtr);
                        if (dataprov.UserId != null && !dataprov.UserId.Equals(""))
                        {
                            dpnode.Attributes.Append(dpUidAtr);
                            dpnode.Attributes.Append(dpPwdAtr);
                        }
                        dpnode.Attributes.Append(dpDefaultAtr);
                        dbNode.AppendChild(dpnode);
                    }

                    foreach (TableDefinition tdef in Tables)
                    {
                        XmlNode tbNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Table", "");

                        XmlAttribute tbNameAtr = myXmlDocument.CreateAttribute("Name");
                        tbNameAtr.Value = tdef.Name;
                        tbNode.Attributes.Append(tbNameAtr);

                        foreach (ColumnDefinition cdef in tdef.ColumnArray)
                        {
                            XmlNode colNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Column", "");

                            XmlAttribute colNameAtr = myXmlDocument.CreateAttribute("Name");
                            XmlAttribute colTypeAtr = myXmlDocument.CreateAttribute("Type");
                            XmlAttribute colKeyAtr = myXmlDocument.CreateAttribute("Key");
                            XmlAttribute colNullAtr = myXmlDocument.CreateAttribute("Null");
                            XmlAttribute colDefaultAtr = myXmlDocument.CreateAttribute("Default");
                            XmlAttribute colForeignKeyAtr = myXmlDocument.CreateAttribute("ForeignKey");
                            XmlAttribute colLengthAtr = myXmlDocument.CreateAttribute("Length");

                            colNameAtr.Value = cdef.Name;
                            colTypeAtr.Value = cdef.GetTypeInModelType();
                            colNullAtr.Value = cdef.Nullable.ToString();

                            colNode.Attributes.Append(colNameAtr);
                            colNode.Attributes.Append(colTypeAtr);
                            colNode.Attributes.Append(colNullAtr);

                            colKeyAtr.Value = cdef.GetKeys();
                            if (!colKeyAtr.Value.Equals(""))
                                colNode.Attributes.Append(colKeyAtr);

                            colDefaultAtr.Value = cdef.DefaultValue;
                            if (cdef.DefaultValue != null)
                                colNode.Attributes.Append(colDefaultAtr);

                            if (cdef.ForeignKey != null)
                            {
                                colForeignKeyAtr.Value = cdef.ForeignKey.ToString();
                                colNode.Attributes.Append(colForeignKeyAtr);
                            }
                            colLengthAtr.Value = cdef.Length.ToString();
                            if (cdef.Length != -1)
                                colNode.Attributes.Append(colLengthAtr);

                            tbNode.AppendChild(colNode);
                        }
                        dbNode.AppendChild(tbNode);
                    }
                    rootNode.ReplaceChild(dbNode, node1);
                    myXmlDocument.Save(XmlFilePath);
                    return;
                }
            }
        }
        /// <summary>
        /// Cancel all changes made to Properties on this object.
        /// All Properties will be reset with the values from the model file.
        /// </summary>
        public void CancelChanges()
        {
            Init();
            ReadOptions();
        }
        #endregion

        #region Options
        private void ReadOptions()
        {
            if (XmlFilePath == null)
                throw new NullReferenceException("Error: Model file not selected.");

            XmlDocument myXmlDocument = new XmlDocument();
            myXmlDocument.Load(XmlFilePath);

            XmlNode rootNode;
            rootNode = myXmlDocument.DocumentElement;

            if (rootNode.Name.ToUpper() != "MODEL")
                throw new Exception("Model file format incorrect.");

            foreach (XmlNode node1 in rootNode.ChildNodes)
            {
                if (node1.Name.ToUpper() == "OPTIONS")
                {
                    foreach (XmlNode node2 in node1.ChildNodes)
                    {
                        switch (node2.Name.ToUpper())
                        {
                            case "EXPORT":
                                try{
                                option_recursive = node2.Attributes["Recursive"] == null ? false :
                                    Convert.ToBoolean(node2.Attributes["Recursive"].Value);
                                }
                                catch (FormatException ex)
                                { option_recursive = false; }
                                break;
                            case "EMAIL":
                                option_emailserver = new EmailServer();
                                if (node2.Attributes["Host"] == null)
                                    break;
                                option_emailserver.Host = node2.Attributes["Host"].Value.ToString();
                                option_emailserver.Port = node2.Attributes["Port"] == null ? 25 :
                                    Convert.ToInt32(node2.Attributes["Port"].Value);
                                option_emailserver.EnableSsl = node2.Attributes["SSL"] == null ? false :
                                    Convert.ToBoolean(node2.Attributes["SSL"].Value);
                                option_emailserver.FromAddress = node2.Attributes["FromAddress"] == null ? null :
                                    node2.Attributes["FromAddress"].Value.ToString();
                                if (node2.Attributes["AccountName"] == null)
                                    option_emailserver.UseDefaultCredentials = true;
                                else
                                {
                                    option_emailserver.UseDefaultCredentials = false;
                                    option_emailserver.AccountName = node2.Attributes["AccountName"].Value.ToString();
                                    option_emailserver.Password = node2.Attributes["Password"] == null ? null :
                                        node2.Attributes["Password"].Value.ToString();
                                }
                                break;
                            case "LOG":
                                option_logSql = node2.Attributes["Sql"] == null ? false : Convert.ToBoolean(node2.Attributes["Sql"].Value);
                                break;
                            case "SAVE":
                                option_save_auto = node2.Attributes["Auto"] == null ? false :Convert.ToBoolean(node2.Attributes["Auto"].Value);
                                option_save_request = node2.Attributes["Request"] == null ? true : Convert.ToBoolean(node2.Attributes["Request"].Value);
                                break;
                            default:
                                break;
                        }
                    }
                }
            }
        }

        public void SaveOptions()
        {
            XmlDocument myXmlDocument = new XmlDocument();
            myXmlDocument.Load(XmlFilePath);

            XmlNode rootNode;
            rootNode = myXmlDocument.DocumentElement;

            foreach (XmlNode node1 in rootNode.ChildNodes)
            {
                if (node1.Name.ToUpper() == "OPTIONS")
                {
                    XmlNode optionsNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Options", "");
                    XmlNode exportNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Export", "");
                    XmlAttribute recAtr = myXmlDocument.CreateAttribute("Recursive");
                    recAtr.Value = option_recursive.ToString();

                    exportNode.Attributes.Append(recAtr);
                    optionsNode.AppendChild(exportNode);

                    if (option_emailserver != null)
                    {
                        XmlNode emailNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Email", "");
                        XmlAttribute hostAtr = myXmlDocument.CreateAttribute("Host");
                        XmlAttribute sslAtr = myXmlDocument.CreateAttribute("SSL");
                        XmlAttribute portAtr = myXmlDocument.CreateAttribute("Port");
                        XmlAttribute accNameAtr = myXmlDocument.CreateAttribute("AccountName");
                        XmlAttribute passAtr = myXmlDocument.CreateAttribute("Password");
                        XmlAttribute fromAtr = myXmlDocument.CreateAttribute("FromAddress");

                        hostAtr.Value = option_emailserver.Host;
                        sslAtr.Value = option_emailserver.EnableSsl.ToString();
                        portAtr.Value = option_emailserver.Port.ToString();
                        accNameAtr.Value = option_emailserver.AccountName;
                        passAtr.Value = option_emailserver.Password;
                        fromAtr.Value = option_emailserver.FromAddress;

                        emailNode.Attributes.Append(hostAtr);
                        emailNode.Attributes.Append(sslAtr);
                        emailNode.Attributes.Append(portAtr);

                        if(option_emailserver.FromAddress != null)
                            emailNode.Attributes.Append(fromAtr);
                        if (!option_emailserver.UseDefaultCredentials)
                        {
                            if(option_emailserver.AccountName != null)
                                emailNode.Attributes.Append(accNameAtr);
                            if(option_emailserver.Password != null)
                                emailNode.Attributes.Append(passAtr);
                        }

                        optionsNode.AppendChild(emailNode);                        
                    }
                    XmlNode logNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Log", "");
                    XmlAttribute sqlAtr = myXmlDocument.CreateAttribute("Sql");
                    sqlAtr.Value = option_logSql.ToString();

                    logNode.Attributes.Append(sqlAtr);
                    optionsNode.AppendChild(logNode);

                    XmlNode saveNode = myXmlDocument.CreateNode(XmlNodeType.Element, "Save", "");
                    XmlAttribute saveAtr1 = myXmlDocument.CreateAttribute("Auto");
                    XmlAttribute saveAtr2 = myXmlDocument.CreateAttribute("Request");
                    saveAtr1.Value = option_save_auto.ToString();
                    saveAtr2.Value = option_save_request.ToString();

                    saveNode.Attributes.Append(saveAtr1);
                    saveNode.Attributes.Append(saveAtr2);
                    optionsNode.AppendChild(saveNode);

                    rootNode.ReplaceChild(optionsNode, node1);
                    myXmlDocument.Save(XmlFilePath);
                }
            }
        }
        #endregion
    }
}

